﻿
Imports System.Data.SqlClient
Imports AppsUtility.EncDec
Imports System.Configuration
Public Class frmServiceJournal


    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationManager.AppSettings("SQLString"))
    Dim SqlCon As New SqlConnection(strCon)

    Dim SerJourId As Integer

    Private Sub frmServiceJournal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call toLoadCustomers()
        Call toLoadServiceLocation()
        Call toLoadServiceTypes()
        Call toLoadSalesmen()
        Call toLoadServiceJournals()
    End Sub

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        If cmdSave.Text = "Save" Then
            If MsgBox("Do you want to Save the Service Journal", MsgBoxStyle.YesNo, "Inventory Manager") = MsgBoxResult.Yes Then
                Call toSaveServiceJournal()
            End If

        Else
            Call toUpdateServiceJournal()
        End If
    End Sub

    Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
        If cmdExit.Text = "Cancel" Then
            cmdSave.Text = "Save"
            cmdExit.Text = "Exit"
            txtRemarks.Clear()
            dtpServDate.Value = Now.Date.ToShortDateString
            cmbCustomer.SelectedIndex = 0
            cmbSalesman.SelectedIndex = 0
            cmbType.SelectedIndex = 0
            cmbLocation.SelectedIndex = 0
            SerJourId = 0
        Else
            Me.Close()
        End If
    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgSerJour.CellDoubleClick

        SerJourId = dgSerJour.SelectedRows(0).Cells(0).Value
        cmbCustomer.Text = dgSerJour.SelectedRows(0).Cells(1).Value
        cmbSalesman.Text = dgSerJour.SelectedRows(0).Cells(2).Value
        cmbType.Text = dgSerJour.SelectedRows(0).Cells(3).Value
        cmbLocation.Text = dgSerJour.SelectedRows(0).Cells(4).Value
        dtpServDate.Value = Convert.ToDateTime(dgSerJour.SelectedRows(0).Cells(5).Value)
        txtRemarks.Text = dgSerJour.SelectedRows(0).Cells(6).Value
        cmdSave.Text = "Update"
        cmdExit.Text = "Cancel"

    End Sub
    Sub toLoadServiceJournals()
        Try
            Dim StrSQL As String
            StrSQL = "select a.JourId,d.CUSNAME,e.Salesman,b.TypeName,c.LocName ,a.DoneBy,a.Remarks from ServiceJournal a,ServiceType b ,ServiceLocation c, Customer d , Salesman e where a.cuscode=d.CUSCODE and a.SalesmanId=e.SalesmanId and a.SerTypeId=b.TypeId and a.SerLoc=c.LocId "

            Dim da As New SqlDataAdapter(StrSQL, SqlCon)
            Dim ds As New DataSet("Journals")
            da.Fill(ds, "Journals")

            dgSerJour.DataSource = ds.Tables(0)

        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Sub

    Sub toUpdateServiceJournal()
        Try
            Dim StrSQL As String
            StrSQL = " Update ServiceJournal set CusCode= '" & cmbCustomer.SelectedValue & "', SalesmanId= " & cmbSalesman.SelectedValue & ",SerTypeId=" & cmbType.SelectedValue & ", SerLoc=" & cmbLocation.SelectedValue & ",DoneBy='" & dtpServDate.Value.ToString("yyyy-MM-dd") & "',Remarks='" & txtRemarks.Text.Trim & "' Where JourId=" & SerJourId

            Dim cmd As New SqlCommand(StrSQL, SqlCon)
            SqlCon.Open()
            cmd.ExecuteNonQuery()
            SqlCon.Close()
            SerJourId = 0
            MsgBox("Updated Succesfully", 64, "Inventory Manager")
            Call toLoadServiceJournals()
            cmdSave.Text = "Save"
            cmdExit.Text = "Exit"
            txtRemarks.Clear()
            dtpServDate.Value = Now.ToShortDateString
        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Sub
    Sub toSaveServiceJournal()
        Try
            Dim StrSQL As String
            StrSQL = "Insert into ServiceJournal (CusCode,SalesmanId,SerTypeId,SerLoc,DoneBy,Remarks) values ('" & cmbCustomer.SelectedValue & "', " & cmbSalesman.SelectedValue & "," & cmbType.SelectedValue & "," & cmbLocation.SelectedValue & ",'" & dtpServDate.Value.ToString("yyyy-MM-dd") & "','" & txtRemarks.Text.Trim & "')"
            Dim cmd As New SqlCommand(StrSQL, SqlCon)
            SqlCon.Open()
            cmd.ExecuteNonQuery()
            SqlCon.Close()
            MsgBox("Saved Succesfully", 64, "Inventory Manager")
            txtRemarks.Clear()
            dtpServDate.Value = Now.ToShortDateString
            Call toLoadServiceJournals()
        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Sub
    Sub toLoadServiceLocation()
        Try
            Dim StrSQL As String
            StrSQL = "Select LocName,LocId from ServiceLocation"

            Dim da As New SqlDataAdapter(StrSQL, SqlCon)
            Dim ds As New DataSet("Location")
            da.Fill(ds, "Location")

            cmbLocation.DataSource = ds.Tables(0)
            cmbLocation.DisplayMember = "LocName"
            cmbLocation.ValueMember = "LocId"

        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Sub

    Sub toLoadServiceTypes()
        Try
            Dim StrSQL As String
            StrSQL = "Select TypeName,TypeId from ServiceType"

            Dim da As New SqlDataAdapter(StrSQL, SqlCon)
            Dim ds As New DataSet("ServiceType")
            da.Fill(ds, "ServiceType")

            cmbType.DataSource = ds.Tables(0)
            cmbType.DisplayMember = "TypeName"
            cmbType.ValueMember = "TypeId"

        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Sub
    Sub toLoadSalesmen()
        Try
            Dim StrSQL As String
            StrSQL = "Select Salesman,SalesmanId from Salesman"

            Dim da As New SqlDataAdapter(StrSQL, SqlCon)
            Dim ds As New DataSet("Salesman")
            da.Fill(ds, "Salesman")

            cmbSalesman.DataSource = ds.Tables(0)
            cmbSalesman.DisplayMember = "Salesman"
            cmbSalesman.ValueMember = "SalesmanId"

        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Sub
    Sub toLoadCustomers()
        Try
            Dim StrSQL As String
            StrSQL = "Select CusName,CusCode from Customer"

            Dim da As New SqlDataAdapter(StrSQL, SqlCon)
            Dim ds As New DataSet("Customer")
            da.Fill(ds, "Customer")

            cmbCustomer.DataSource = ds.Tables(0)
            cmbCustomer.DisplayMember = "CusName"
            cmbCustomer.ValueMember = "CusCode"
        Catch ex As Exception
            MsgBox(ex.ToString, 16, "Inventory Manager")
        End Try
    End Sub


End Class